﻿Imports System.Data.SqlClient

Public Class frmChartOfAccountsTranslation

    Sub Fill()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From AccDictionary Order By AccNameEng", cnn)
            Dim Reader As SqlDataReader

            Me.GridDictionary.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.GridDictionary.Rows.Add(New String() {Reader.Item(0), Reader.Item(1), Reader.Item(2), "Update", "Delete"})
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnAdd_Click(sender As System.Object, e As System.EventArgs) Handles btnAdd.Click
        Me.ErrProv.Clear()
        Try
            If Me.txtAccNameEng.Text.Trim.Length = 0 Then
                Me.ErrProv.SetError(Me.txtAccNameEng, "Please fill in all details")

            ElseIf Me.txtAccNameAr.Text.Trim.Length = 0 Then
                Me.ErrProv.SetError(Me.txtAccNameAr, "Please fill in all details")
            End If

            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Insert Into AccDictionary (AccNameEng,AccNameAr) Values (@AccNameEng,@AccNameAr)", cnn)

            cmd.Parameters.AddWithValue("@AccNameEng", Me.txtAccNameEng.Text.Trim)
            cmd.Parameters.AddWithValue("@AccNameAr", Me.txtAccNameAr.Text.Trim)

            cnn.Open()
            cmd.ExecuteNonQuery()
            cnn.Close()

            Fill()

            Me.txtAccNameEng.Clear()
            Me.txtAccNameAr.Clear()
            Me.txtAccNameEng.Focus()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmChartOfAccountsTranslation_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        Fill()
    End Sub

    Private Sub GridDictionary_CellClick(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridDictionary.CellClick
        If e.ColumnIndex = 3 Then
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand
                Dim Trans As SqlTransaction

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                'Save Contacts
                For Each row As DataGridViewRow In Me.GridDictionary.Rows
                    cmd.Parameters.Clear()
                    cmd.CommandText = "Update AccDictionary Set AccNameEng=@AccNameEng,AccNameAr=@AccNameAr Where SNo=" & row.Cells(0).Value

                    cmd.Parameters.AddWithValue("@AccNameEng", row.Cells(1).Value)
                    cmd.Parameters.AddWithValue("@AccNameAr", row.Cells(2).Value)
                    cmd.ExecuteNonQuery()
                Next

                Trans.Commit()
                cnn.Close()

                MsgBox("Updated Successfully!", MsgBoxStyle.Information, Me.Text)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = 4 Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Try
                    Me.Cursor = Cursors.WaitCursor

                    Dim cmd As New SqlCommand("Delete From AccDictionary Where SNo=" & Me.GridDictionary.CurrentRow.Cells(0).Value, cnn)

                    cnn.Open()
                    cmd.ExecuteNonQuery()
                    cnn.Close()

                    Fill()

                    Me.Cursor = Cursors.Default
                Catch ex As Exception
                    Me.Cursor = Cursors.Default
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    MsgBox(ex.ToString)
                End Try
            End If
        End If
    End Sub
End Class